import os
import sqlite3
import time

import pandas as pd

def format_time(t):
    # 将字符串解析为时间元祖
    if len(t) < 7:
        t = time.strptime(t, "%b-%y")
    else:
        t = time.strptime(t, "%b-%Y")
    # 将时间元组格式化为指定的样式
    t = time.strftime("%Y-%m", t)
    return t

def dw_num():
    db_path: str = "db/user.db"
    path = "data/ods" #文件夹目录
    files= os.listdir(path) #得到文件夹下的所有文件名称
    filenames = []
    lens = []
    datas = pd.DataFrame()
    print(datas)
    for file in files: #遍历文件夹
         if not os.path.isdir(file): #判断是否是文件夹，不是文件夹才打开
              f = open(path+"/"+file); #打开文件
              data = pd.read_csv(path + "/" + file)
              data = data.dropna(subset=['Result'])  # 删除缺失值
              data = data[data['Result'] != 0]  # 删除0值
              data["Updated"] = data["Updated"].map(format_time)
              # print(data)
              datas = pd.concat([datas, data])

    conn = sqlite3.connect(db_path)
    # 按Company型号分组
    company_group = datas.sort_values(["Company"], ascending=[1]).groupby("Company")
    lables = []
    nums = []
    for lable, option in company_group:
         lables.append(lable)
         nums.append(len(option))
    c = conn.cursor()
    data = pd.DataFrame({"name": lables, "num": nums})
    # print(data)
    with open(r"dw/script/create_num_template", "r", True, 'UTF-8') as f:
        sql = f.read().format("company", "company")
        c.executescript(sql)
    data.to_sql(f"num_{'company'}", conn, if_exists="append", index=False)
    conn.commit()
    c.close()

    # 按Processor型号分组
    processor_group = datas.groupby("Processor")
    company_group = datas.sort_values(["Processor"], ascending=[1]).groupby("Processor")
    lables = []
    nums = []
    for lable, option in company_group:
         lables.append(lable)
         nums.append(len(option))
    c = conn.cursor()
    data = pd.DataFrame({'name': lables, 'num': nums})
    # print(data)
    with open("dw/script/create_num_template", "r", True, 'UTF-8') as f:
        sql = f.read().format("processor", "processor")
        c.executescript(sql)
    data.to_sql(f"num_{'processor'}", conn, if_exists="append", index=False)
    conn.commit()
    c.close()


    # 按Update型号分组
    processor_group = datas.groupby("Updated")
    company_group = datas.sort_values(["Updated"], ascending=[1]).groupby("Updated")
    lables = []
    nums = []
    data = pd.DataFrame({'name': lables, 'num': nums})
    for lable, option in company_group:
         lables.append(lable)
         nums.append(len(option))
    c = conn.cursor()
    data = pd.DataFrame({'name': lables, 'num': nums})
    # print(data)
    with open("dw/script/create_num_template", "r", True, 'UTF-8') as f:
        sql = f.read().format("updated", "updated")
        c.executescript(sql)
    data.to_sql(f"num_{'updated'}", conn, if_exists="append", index=False)
    conn.commit()
    c.close()

    conn.close()
